unit hadesqldataaccessor;

{$mode objfpc}{$H+}

interface

uses
  Classes,
  SysUtils,
  hadebasedataaccessor,
  hademapbase,
  hadecriteria,
  hadebasevisitor,
  hadeconnectionmanager,
  hadedataset,
  hadeobjectfactory;

type
  { THadeSQLDataAccessorAbs }

  THadeSQLDataAccessorAbs = class(THadeBaseDataAccessor)
  protected
    function getStringLiteral(const AValue: variant): string;
    function getColumnString(const ACols: THadeMapSimpleValue): string; overload;
    function getColumnString(ACols: TStringList): string; overload;
    function getParamsString(const ACols: THadeMapSimpleValue): string;
    function getParamsSETString(const ACols: THadeMapSimpleValue): string;

    function getValuesString(const ACols: THadeMapSimpleValue): string;
    function getSETString(const ACols: THadeMapSimpleValue): string;

    function simpleRead(const ATableName: string; const ACols: TStringList): string;
    function ReadOrderBy(const ATableName: string;
      const ACols, AOrderBy: TStringList;
      AOrderType: THadeOrderType = oASC): string;
    function ReadHelper(const ATableName: string;
      const ACols, AOrderBy: TStringList; const whereClause: string;
      AOrderType: THadeOrderType): string;
  public
    procedure Insert(const ATableName: string; const AObject: THadeVisited;
      ACols: THadeMapSimpleValue; const Execute: boolean = True); override;

    procedure Update(const ATableName: string; const AObject: THadeVisited;
      ACols: THadeMapSimpleValue; const whereClause: string;
      const Execute: boolean = True); override;

    procedure Delete(const ATableName, whereclause: string;
      const AObject: THadeVisited; const Execute: boolean = True); override;
  end;

  { THadeFirebirdAccessor }

  THadeFirebirdAccessor = class(THadeSQLDataAccessorAbs)
  public
    procedure Read(const ATableName: string; const AObject: THadeVisited;
      ACols, AOrderBy: TStringList; const whereClause: string = '';
      AOrderType: THadeOrderType = oASC; const limit: integer = 0;
      const offset: integer = 0); override;
  end;

  { THadeSQLiteAccessor }
  { SQLite }
  THadeSQLiteAccessor = class(THadeSQLDataAccessorAbs)
  public
    procedure Read(const ATableName: string; const AObject: THadeVisited;
      ACols, AOrderBy: TStringList; const whereClause: string = '';
      AOrderType: THadeOrderType = oASC; const limit: integer = 0;
      const offset: integer = 0); override;
  end;

implementation

uses
  variants,
  hadeobject,
  hadeutils,
  hadeopfmanager;

{ local function }
function OrderTypeToString(const AOrder: THadeOrderType): string;
begin
  case AOrder of
    oAsc: Result := 'ASC';
    oDesc: Result := 'DESC';
  end;
end;

{ THadeSQLiteAccessor }

procedure THadeSQLiteAccessor.Read(const ATableName: string;
  const AObject: THadeVisited; ACols, AOrderBy: TStringList;
  const whereClause: string; AOrderType: THadeOrderType; const limit: integer;
  const offset: integer);
var
  tmp: string;
begin
  tmp := ReadHelper(ATableName, ACols, AOrderBy, whereclause, AOrderType);

  if Abs(offset) > 0 then
    tmp := tmp + ' LIMIT ' + IntToStr(Abs(limit)) + ',' + IntToStr(Abs(offset));

  FDataSet.sql.Text := tmp;
  FDataSet.Open;
  if AObject is THadeObjectList then
    fObjectFactory.RowToObjectList(Aobject, FDataset)
  else
    fObjectFactory.RowToObject(Aobject, FDataset);
end;

{ THadeSQLDataAccessorAbs }

function THadeSQLDataAccessorAbs.getStringLiteral(const AValue: variant): string;
begin
  if (varToStr(AValue) = '0') or (varToStr(AValue) = '') then
  begin
    Result := 'NULL';
    exit;
  end;
  if VarIsStr(AValue) then
    Result := QuotedStr(AValue)
  else
    Result := varToStr(AValue);
end;

function THadeSQLDataAccessorAbs.getColumnString(
  const ACols: THadeMapSimpleValue): string;
var
  iloop: integer;
  fields: string;
begin
  for iloop := 0 to pred(ACols.Count) do
  begin
    fields := fields + ACols.NameOfIndex(iloop) + ',';
  end;
  System.Delete(fields, Length(fields), 1);
  Result := fields;
end;

function THadeSQLDataAccessorAbs.getColumnString(ACols: TStringList): string;
var
  iloop: integer;
  fields: string;
begin
  for iloop := 0 to pred(ACols.Count) do
  begin
    fields := fields + ACols[iloop] + ',';
  end;
  Result := deleteLastChar(fields);
end;

function THadeSQLDataAccessorAbs.getParamsString(
  const ACols: THadeMapSimpleValue): string;
var
  iloop: integer;
  tmp, params: string;
begin
  for iloop := 0 to pred(ACols.Count) do
  begin
    params := ':' + ACols.NameOfIndex(iloop);
    tmp := tmp + params + ',';
  end;
  System.Delete(tmp, Length(tmp), 1);
  Result := tmp;
end;

function THadeSQLDataAccessorAbs.getParamsSETString(
  const ACols: THadeMapSimpleValue): string;
var
  iloop: integer;
  params: string;
begin
  Result := '';
  for iloop := 0 to pred(ACols.Count) do
  begin
    params := ':' + ACols.NameOfIndex(iloop);
    Result := Result + ACols.NameOfIndex(iloop) + ' = ' + params + ',';
  end;
  deleteLastChar(Result);
end;

function THadeSQLDataAccessorAbs.getValuesString(
  const ACols: THadeMapSimpleValue): string;
var
  iloop: integer;
  params: string;
begin
  for iloop := 0 to pred(ACols.Count) do
  begin
    params := params + getStringLiteral(ACols.Items[iloop].Value) + ',';
  end;
  System.Delete(params, Length(params), 1);
  Result := params;
end;

function THadeSQLDataAccessorAbs.getSETString(
  const ACols: THadeMapSimpleValue): string;
var
  iloop: integer;
begin
  Result := '';
  for iloop := 0 to pred(ACols.Count) do
  begin
    Result := Result + ACols.NameOfIndex(iloop) + ' = ' +
      getStringLiteral(ACols.Items[iloop].Value) + ',';
  end;
  deleteLastChar(Result);
end;

function THadeSQLDataAccessorAbs.simpleRead(const ATableName: string;
  const ACols: TStringList): string;
var
  tmp: string;
begin
  tmp := 'SELECT %s FROM %s';
  tmp := Format(tmp, [getColumnString(ACols), ATableName]);
  Result := tmp;
end;

function THadeSQLDataAccessorAbs.ReadOrderBy(const ATableName: string;
  const ACols, AOrderBy: TStringList; AOrderType: THadeOrderType): string;
var
  tmp: string;
begin
  tmp := ' ORDER BY %s %s';
  if getColumnString(AOrderBy) = '' then
    tmp := ''
  else
    tmp := Format(tmp, [getColumnString(AOrderBy), OrderTypeToString(AOrderType)]);
  Result := tmp;
end;

function THadeSQLDataAccessorAbs.ReadHelper(const ATableName: string;
  const ACols, AOrderBy: TStringList; const whereClause: string;
  AOrderType: THadeOrderType): string;
begin
  if (AOrderBy.Count <= 0) and (whereClause = '') then
  begin
    Result := self.simpleRead(ATableName, ACols);
  end
  else if (AOrderBy.Count > 0) and (whereclause = '') then
  begin
    Result := simpleRead(ATableName, ACols) + self.ReadOrderBy(
      ATableName, ACols, AOrderBy, AOrderType);
  end
  else if (whereclause <> '') and (AOrderBy.Count > 0) then //with whereclause
  begin
    Result := simpleRead(ATableName, ACols) + ' WHERE ' + whereClause +
      self.ReadOrderBy(ATableName, ACols, AOrderBy, AOrderType);
  end
  else if (whereClause <> '') and not (Assigned(AOrderBy)) then
    Result := simpleRead(ATableName, ACols) + ' WHERE ' + whereClause;
end;

procedure THadeSQLDataAccessorAbs.Insert(const ATableName: string;
  const AObject: THadeVisited; ACols: THadeMapSimpleValue; const Execute: boolean);
var
  tmp, values: string;
begin
  tmp := 'INSERT INTO %s(%s) VALUES(%s)';

  if fWithoutParams then
    values := self.getValuesString(ACols)
  else
    values := self.getParamsString(ACols);

  FDataSet.SQL.Text := Format(tmp, [ATableName, getColumnString(ACols), Values]);
  if not fWithoutParams then
    fObjectFactory.ObjectToRow(Aobject, FDataset);

  if Execute = True then
    FDataSet.ExecSQL;

  if GHadeOPFManager.OIDStrategy = oidAutoInc then
    THadeObject(AObject).OID.IntegerOID := FDataSet.getLastInsertID(ATableName);
end;

procedure THadeSQLDataAccessorAbs.Update(const ATableName: string;
  const Aobject: THadeVisited; ACols: THadeMapSimpleValue;
  const whereClause: string; const Execute: boolean);
var
  tmp, values: string;
begin
  tmp := 'UPDATE %s SET %s WHERE ' + whereClause;

  if fWithoutParams then
    values := self.getSETString(ACols)
  else
    values := self.getParamsSETString(ACols);

  FDataSet.SQL.Text := Format(tmp, [ATableName, values]);

  if not fWithoutParams then
    fObjectFactory.ObjectToRow(Aobject, FDataset);

  if Execute then
    FDataSet.ExecSQL;
end;

procedure THadeSQLDataAccessorAbs.Delete(const ATableName, whereclause: string;
  const Aobject: THadeVisited; const Execute: boolean);
var
  tmp: string;
begin
  tmp := 'DELETE FROM ' + ATableName + ' WHERE ' + whereclause;
  FDataSet.SQL.Text := tmp;

  if Execute then
    FDataSet.ExecSQL;
end;

{ THadeFirebirdAccessor }

procedure THadeFirebirdAccessor.Read(const ATableName: string;
  const AObject: THadeVisited; ACols, AOrderBy: TStringList;
  const whereClause: string; AOrderType: THadeOrderType; const limit: integer;
  const offset: integer);
var
  tmp: string;
begin
  tmp := ReadHelper(ATableName, ACols, AOrderBy, whereclause, AOrderType);

  if Abs(offset) > 0 then
    tmp := tmp + ' ROWS ' + IntToStr(Abs(offset)) + ' TO ' + IntToStr(Abs(Succ(limit)));

  FDataSet.sql.Text := tmp;
  FDataSet.Open;
  if AObject is THadeObjectList then
    fObjectFactory.RowToObjectList(Aobject, FDataset)
  else
    fObjectFactory.RowToObject(Aobject, FDataset);
end;

end.

